BigQueryでマルチステートメントトランザクションがGAになりました

#Google BigQuery

#Google Cloud (GCP)

nayu.t.s

2022.10.24

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

少し時間が経ってしまいましたが、2022/10/10のリリースで、BigQueryでマルチステートメントトランザクションがGAになりました。

BigQueryでプレビューであったマルチステートメントトランザクションがGAに?複数のステートメントをまとめてDMLを実行可能?ユーザーの見るデータマートの更新やDWHの移行がやりやすくなります。https://t.co/EI5vzL4vu3

— Yuta.H (@yutah_3) October 11, 2022

リリースノートはこちらです。

マルチステートメントトランザクションについて

複数のクエリにまたがるトランザクションを使い、クエリの結果に応じてコミットやロールバックを実行できます。

BEGIN TRANSACTIONステートメントでトランザクションを開始し、以下の３パターンで終了します。

クエリがCOMMIT TRANSACTIONステートメントを実行：トランザクション内で行われたすべての変更がコミットされる。
クエリがROLLBACK TRANSACTIONステートメントを実行：トランザクション内で行われたすべての変更がロールバックされる。
クエリが上のいずれかのステートメントに到達する前に終了：トランザクション内で行われたすべての変更がロールバックされる。

詳細は以下の公式ドキュメントをご確認ください。

データ分析基盤でのユースケース例

単純に、複数のクエリをまとめて実行する際に複数のクエリにまたがってトランザクションを使いたいときに使えます。

加えて個人的に特に気に入っている例は、クエリのスケジューリングとの組み合わせです。

例えば定期的にINSERTを実行する際、失敗してリトライすると同じデータが入ってしまいます。そのため該当レコードを削除してから挿入するような複数のクエリで冪等性を担保する必要があります。

このような組み合わせでも、途中で何らかの理由で失敗してしまうと困るので、トランザクションを使ってエラー時にロールバックされるようにしたいです。クエリのスケジューリングは定期的に設定したクエリを実行してくれる機能です。とても便利な機能で、私はお世話になっているのですが、この機能単体では失敗時に複雑なケアをすることは難しいので、マルチステートメント　トランザクションを使っておくと、安心して運用できます。

具体的なイメージとしては、以前に書いた以下の記事をご紹介します。

※この例ではテーブルのコメントなどを元のテーブルのまま使い回したかったので、INSERTを使っています。

トランザクションの同時実行時の挙動の確認

上記ドキュメントのうち、トランザクションの同時実行はどのような挙動になるのか見たかったので試してみました。

先に記載したブログのクエリと同等のものを、UIから続けて２個実行し、同じテーブル内の行を変更するトランザクションが同時実行された際に、後から実行されている方が失敗することを確認できました。

このようなエラーが出た場合は、少し時間を置いてから再実行すると上手くいくかもしれません。

連続して同じクエリを実行する

なお、実行したクエリは以下です。データセットとテーブルは、上記ブログの通りあらかじめ作成されていることとします。

BEGIN TRANSACTION;

-- TEMPテーブルを作成する
CREATE TEMP TABLE stg_table(ip STRING, port INT, type STRING)
AS
  -- 対象データから一時データを作成する
  SELECT
    lt.ip as ip,
    lt.port as port,
    tt.type as type
  FROM sample_dataset.log lt
  LEFT JOIN sample_dataset.type tt
  ON lt.port = tt.port
  WHERE lt.run_date = DATE('2022-10-21', 'Asia/Tokyo');

-- 重複箇所を削除する
DELETE FROM `sample_dataset.result` mart
WHERE mart.run_date = DATE('2022-10-21', 'Asia/Tokyo');

-- 新しい結果をInsertする
INSERT `sample_dataset.result` (ip, port, type, run_date)
SELECT 
  ip,
  port,
  type,
  DATE('2022-10-21', 'Asia/Tokyo') run_date
FROM stg_table;

-- 一時テーブルを削除する
DROP TABLE stg_table;

COMMIT TRANSACTION;

ほかにも、同ドキュメントの制限事項にいくつかの点が記載されているので、導入前に確認しておきましょう。